<?php

	// Make sure we are authenticated
	require_once('scripts/authentication.php');
	
	// Check if the file type has been provided
	if (!isset($_POST["fileType"]))
	{
		exit("Error: You must use the Export page to export data!");
	}
	
	// Get the file type to export
	$fileType = $_POST["fileType"];
	
	// Filename to generate
	$fileName = "eLibrary_" . date("Ymd") . "." . $fileType;
	
	// Is this a backup (DEFAULT = no)
	$isBackup = false;
	
	// Check to see if a backup needs to be done
	if ($fileType == "sql-backup")
	{
		// This is a backup
		$isBackup = true;
		
		// The file type is actually sql
		$fileType = "sql";
		
		// Adjust the fileName accordingly
		$fileName = "eLibrary_full_" . date("Ymd") . "." . $fileType;
	}
	
	if ($fileType == "sql")
	{
		/* SQL Output Process */
		
		// Establish a connection to the MySQL database
		require_once('scripts/dbconnection.php');
		
		// Open the output file
		$outFile = fopen("importexport/" . $fileName, "w");
		
		// If the file is a full backup we need to include some additional statements
		if ($isBackup == true)
		{
			/* Drop Table Statements */
			fwrite($outFile, "DROP TABLE IF EXISTS BOOKSUBJECTS;\n");
			fwrite($outFile, "DROP TABLE IF EXISTS BOOK;\n");
			fwrite($outFile, "DROP TABLE IF EXISTS AUTHOR;\n");
			fwrite($outFile, "DROP TABLE IF EXISTS SUBJECTS;\n");
		
			// Leave a blank line
			fwrite($outFile, "\n");
		
			/* Create Table Statements */
			fwrite($outFile, "CREATE TABLE SUBJECTS (SubjectID INT NOT NULL, SubjectName VARCHAR(50) NOT NULL, PRIMARY KEY (SubjectID));\n");
			fwrite($outFile, "CREATE TABLE AUTHOR (AuthorID INT NOT NULL, AuthorName VARCHAR(100) NOT NULL, PRIMARY KEY (AuthorID));\n");
			fwrite($outFile, "CREATE TABLE BOOK (BookID INT NOT NULL, CallNo VARCHAR(10) NOT NULL, Title VARCHAR(100) NOT NULL, ISBN VARCHAR(20), AuthorID INT, Publisher VARCHAR(50), Description VARCHAR(255), PRIMARY KEY (BookID), FOREIGN KEY (AuthorID) REFERENCES AUTHOR(AuthorID));\n");
			fwrite($outFile, "CREATE TABLE BOOKSUBJECTS (BookID) INT NOT NULL, SubjectID INT NOT NULL, PRIMARY KEY (BookID, SubjectID), FOREIGN KEY (BookID) REFERENCES BOOK(BookID), FOREIGN KEY (SubjectID) REFERENCES SUBJECTS(SubjectID));\n");
		
			// Leave a blank line
			fwrite($outFile, "\n");
		}
		
		/* Output Subjects Table */	
		$result = mysql_query("SELECT * FROM SUBJECTS");
		while ($row = mysql_fetch_assoc($result))
		{
			fwrite($outFile, "INSERT INTO SUBJECTS VALUES (" . $row["SubjectID"] . ", '" . $row["SubjectName"] . "');\n");
		}
		
		// Leave a blank line
		fwrite($outFile, "\n");
		
		/* Output Author Table */
		$result = mysql_query("SELECT * FROM AUTHOR");
		while ($row = mysql_fetch_assoc($result))
		{
			fwrite($outFile, "INSERT INTO AUTHOR VALUES (" . $row["AuthorID"] . ", '" . $row["AuthorName"] . "');\n");
		}
		
		// Leave a blank line
		fwrite($outFile, "\n");
		
		/* Output Book Table */
		$result = mysql_query("SELECT * FROM BOOK");
		while ($row = mysql_fetch_assoc($result))
		{
			fwrite($outFile, "INSERT INTO BOOK VALUES (" . $row["BookID"] . ", '". $row["CallNo"] . "', '" . $row["Title"] . "', '" . $row["ISBN"] . "', " . $row["AuthorID"] . ", '" . $row["Publisher"] . "', '" . $row["Description"] . "');\n");
		}
		
		// Leave a blank line
		fwrite($outFile, "\n");
		
		/* Output BookSubjects Table */
		$result = mysql_query("SELECT * FROM BOOKSUBJECTS");
		while ($row = mysql_fetch_assoc($result))
		{
			fwrite($outFile, "INSERT INTO BOOKSUBJECTS VALUES (" . $row["BookID"] . ", " . $row["SubjectID"] . ");\n");
		}
		
		// CLose the output file
		fclose($outFile);
		
		// Close the MySQL connection
		mysql_close();
		
		// Redirect the user to the output file
		header("Location: importexport/" . $fileName);
		
		/* End SQL Output Process */
	}
	else if ($fileType == "csv")
	{
		/* CSV Output Process */
		
		// Establish a connection to the MySQL database
		require_once('scripts/dbconnection.php');
		
		// Open the output file
		$outFile = fopen("importexport/" . $fileName, "w");
		
		/* Output column headers */
		fwrite($outFile, "BookID,CallNo,Title,ISBN,AuthorName,Publisher,Description\n");
		
		/* Output Book Table */
		$result = mysql_query("SELECT * FROM BOOK, AUTHOR WHERE BOOK.AuthorID = AUTHOR.AuthorID");
		while ($row = mysql_fetch_assoc($result))
		{
			fwrite($outFile, $row["BookID"] . "," . $row["CallNo"] . "," . $row["Title"] . "," . $row["ISBN"] . "," . $row["AuthorName"] . "," . $row["Publisher"] . "," . $row["Description"] . "\n");
		}
		
		// CLose the output file
		fclose($outFile);
		
		// Close the MySQL connection
		mysql_close();
		
		// Redirect the user to the output file
		header("Location: importexport/" . $fileName);
		
		/* End CSV Output Process */
	}
	else
	{
		echo "Error: An invalid file type was specified!";
	}
	
?>